package com.elite.groovy.kh;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * @projectName: ngsapitask-davco
 * @package: com.elite.groovy.kh
 * @className: KHExcelUtil
 * @description: TODO
 * @author: LiJT
 * @date: 2023-11-24 15:45
 * @version: 1.0
 */
public class KHExcelUtil {
    private static final Log log = LogFactory.getLog(KHExcelUtil.class);

    private static KHExcelUtil instance = new KHExcelUtil();

    private KHExcelUtil() {
    }

    public static KHExcelUtil getInstance() {
        return instance;
    }

    public String exportExcel(List<LinkedHashMap<String, Object>> list, List<String> head, String excelPath, String filename) throws Exception {
        log.debug("Export Excel 入参文件名:" + filename + " 路径:" + excelPath);
        log.debug("Export Excel 入参表格头:" + head.toString());
        log.debug("Export Excel 入参表数据:" + list.toString());

        Map<String, Object> map = list.get(0);
        Set<String> stringSet = map.keySet();
        ArrayList<String> field = new ArrayList<>(stringSet);

        // 定义一个新的工作簿
        XSSFWorkbook wb = new XSSFWorkbook();

        // 创建一个Sheet页
        XSSFSheet sheet = wb.createSheet("清单数据");
        sheet.createFreezePane(0, 1, 0, 1);

        // head 表格头样式
        CellStyle headCellStyle = wb.createCellStyle();
        // head 设置居中&背景色&冻结
        // headCellStyle.setAlignment(HorizontalAlignment.CENTER);
        headCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        headCellStyle.setFillForegroundColor(IndexedColors.VIOLET.getIndex());
        headCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        headCellStyle.setWrapText(true);

        // head 设置边框
        headCellStyle.setBorderBottom(BorderStyle.THIN);
        headCellStyle.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        headCellStyle.setBorderLeft(BorderStyle.THIN);
        headCellStyle.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        headCellStyle.setBorderRight(BorderStyle.THIN);
        headCellStyle.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        headCellStyle.setBorderTop(BorderStyle.THIN);
        headCellStyle.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());

        // head 设置字体
        Font headFont = wb.createFont();
        headFont.setItalic(false);
        headFont.setStrikeout(false);
        headFont.setBold(true);
        headFont.setFontName("微软雅黑");
        headFont.setColor(IndexedColors.WHITE1.getIndex());
        headFont.setFontHeightInPoints((short) 10);
        headCellStyle.setFont(headFont);


        // even 偶数行样式
        CellStyle evenCellStyle = wb.createCellStyle();
        // even 设置垂直对齐&背景色&自动换行
        evenCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        evenCellStyle.setFillForegroundColor(IndexedColors.WHITE1.getIndex());
        evenCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        evenCellStyle.setWrapText(true);
        // even 设置边框
        evenCellStyle.setBorderBottom(BorderStyle.THIN);
        evenCellStyle.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        evenCellStyle.setBorderLeft(BorderStyle.THIN);
        evenCellStyle.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        evenCellStyle.setBorderRight(BorderStyle.THIN);
        evenCellStyle.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        evenCellStyle.setBorderTop(BorderStyle.THIN);
        evenCellStyle.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        // even 设置字体
        Font evenFont = wb.createFont();
        evenFont.setItalic(false);
        evenFont.setStrikeout(false);
        evenFont.setBold(false);
        evenFont.setFontName("微软雅黑");
        evenFont.setFontHeightInPoints((short) 9);
        evenCellStyle.setFont(evenFont);

        // uneven 奇数数行样式
        CellStyle unevenCellStyle = wb.createCellStyle();
        // uneven 设置垂直对齐&背景色&自动换行
        unevenCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        unevenCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        unevenCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        unevenCellStyle.setWrapText(true);
        // uneven 设置边框
        unevenCellStyle.setBorderBottom(BorderStyle.THIN);
        unevenCellStyle.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        unevenCellStyle.setBorderLeft(BorderStyle.THIN);
        unevenCellStyle.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        unevenCellStyle.setBorderRight(BorderStyle.THIN);
        unevenCellStyle.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        unevenCellStyle.setBorderTop(BorderStyle.THIN);
        unevenCellStyle.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        // uneven 设置字体
        Font unevenFont = wb.createFont();
        unevenFont.setItalic(false);
        unevenFont.setStrikeout(false);
        unevenFont.setBold(false);
        unevenFont.setFontName("微软雅黑");
        unevenFont.setFontHeightInPoints((short) 9);
        unevenCellStyle.setFont(unevenFont);

        // 数据-添加表头
        XSSFRow row1 = sheet.createRow(0);
        row1.setHeightInPoints(20);//目的是想把行高设置成20px

        // 抽取一条样例数据
        List<String> dataList = new ArrayList<>();
        for (Map.Entry<String, Object> entry : list.get(0).entrySet()) {
            log.debug("样例数据key= " + entry.getKey() + " and value= " + entry.getValue());
            dataList.add(entry.getValue().toString());
        }

        for (int i = 0; i < head.size(); i++) {
            XSSFCell cell = row1.createCell(i);

            String tdText = head.get(i);
            cell.setCellValue(tdText);
            cell.setCellStyle(headCellStyle);

            //计算中文个数
            int zh_Count = calculateChineseNumber(tdText);

            //计算所需宽度
            int strLen = (tdText.length() - zh_Count) * 256 + (zh_Count + 1) * 512;
            log.debug("固定[" + tdText + "]列宽:" + strLen);

            // 获取数据样例
            int strddLen = 0;
            try {
                if (list.size() > 0) {
                    String ddText = dataList.get(i);

                    //计算中文个数
                    int zhdd_Count = calculateChineseNumber(ddText);

                    //计算所需宽度
                    strddLen = (ddText.length() - zhdd_Count) * 256 + (zhdd_Count + 1) * 512;
                    log.debug("固定数据样例[" + ddText + "]列宽:" + strddLen);
                }
            } catch (Exception e) {
                log.error("获取数据样例宽度 Exception-", e);
            }

            strLen = Math.max(strddLen, strLen);

            //设置宽度
            if (strLen < 10000) {
                sheet.setColumnWidth(i, strLen);
            } else {
                log.debug("固定列宽超出10000,使用默认值:10000");
                sheet.setColumnWidth(i, 10000);
            }
        }

        // 数据-写入数据
        for (int j = 0; j < list.size(); j++) {
            XSSFRow row = sheet.createRow(j + 1);
            boolean isEven = isEven(j + 2);
            for (int i = 0; i < field.size(); i++) {
                XSSFCell cell = row.createCell(i);
                String CellValue = "";
                try {
                    CellValue = list.get(j).get(field.get(i)).toString();
                } catch (Exception e) {
                    log.warn("Excel Cell Value 空-行数:" + (j + 1));
                }

                cell.setCellValue(CellValue);
                if (isEven) {
                    cell.setCellStyle(evenCellStyle);
                } else {
                    cell.setCellStyle(unevenCellStyle);
                }
            }
        }

        String path = excelPath + filename + ".xlsx";
        File savefile = new File(excelPath);
        if (!savefile.exists()) {
            savefile.mkdirs();
        }
        File file = new File(path);
        FileOutputStream fileOutputStream = new FileOutputStream(file);
        wb.write(fileOutputStream);
        wb.close();
        fileOutputStream.close();
        return path;

    }

    public static boolean isEven(int num) {
        return num % 2 == 0;
    }

    public static int calculateChineseNumber(String str) {
        int count = 0;
        String regEx = "[\\u4e00-\\u9fa5]";
        Pattern pattern = Pattern.compile(regEx);
        Matcher matcher = pattern.matcher(str);
        while (matcher.find()) {
            for (int i = 0; i <= matcher.groupCount(); i++) {
                count = count + 1;
            }
        }
        return count;
    }

    /**
     * @param path:      文件路径
     * @param sheet_num: 是否仅读取第一个
     * @param start_row: 开始行
     * @return List<List>
     * @author LiJT
     * @description 读取文件
     * @date 2024-03-14 18:16
     */
    public static List<List> readExcel(String path, int sheet_num, int start_row) throws Exception {
        List<List> ExcelList = new ArrayList<List>();
        if (path == null) {
            return ExcelList;
        }

        //根据文件位置找到需要读取的excel文件
        File xlsFile = new File(path);
        if (!xlsFile.exists()) {
            return ExcelList;
        }
        String xlsFileExt = getFileExtension(xlsFile.getName());
        log.debug("读取文件名:" + xlsFile.getName() + " 拓展名:" + xlsFileExt);

        //根据路径生成 FileInputStream字节流
        FileInputStream fileInputStream = new FileInputStream(xlsFile);

        //将FileInputStream转换为Workbook
        Workbook workbook = null;
        // 截取路径名,后面的后缀名，判断是xls还是xlsx
        if (xlsFileExt.equalsIgnoreCase("xls")) {
            workbook = new HSSFWorkbook(fileInputStream);
        } else if (xlsFileExt.equalsIgnoreCase("xlsx")) {
            workbook = new XSSFWorkbook(fileInputStream);
        } else {
            return ExcelList;

        }
        int sheet_Nums = workbook.getNumberOfSheets();
        log.debug("总Sheet数量:" + sheet_Nums);

        // 如果指定第一个Sheet,则仅读取第一个Sheet
        if (sheet_num == 1) {
            sheet_Nums = 1;
        }

        for (int a = 0; a < sheet_Nums; a++) {

            // 获取Sheet
            Sheet sheet = workbook.getSheetAt(a);
            String sheet_Name = sheet.getSheetName();

            // 循环获取每一行数据 因为默认第一行为标题行，我们可以从 1 开始循环，如果没有标题行，i从 0 开始
            int sheet_Rows = sheet.getPhysicalNumberOfRows();
            int sheet_Cells = sheet.getRow(0).getPhysicalNumberOfCells();// 以首行列数作为后续所有列列数
            log.debug("Sheet_Name:" + sheet_Name + " 总行数:" + sheet_Rows + " 首行列数:" + sheet_Cells);

            List<List> rowList = new ArrayList<List>();
            for (int i = start_row; i < sheet_Rows; i++) {
                // 循环读取每行
                Row row = sheet.getRow(i);
                int sheet_Cells1 = row.getPhysicalNumberOfCells();
                int sheet_Cells2 = row.getLastCellNum();
                log.debug("当前行:" + i + " 获取不为空的的列个数:" + sheet_Cells1 + " 获取最后一个不为空的列列数:" + sheet_Cells2);

                List<String> cellList = new ArrayList<String>();
                for (int j = 0; j < sheet_Cells; j++) {
                    //log.debug("当前行:" + i + "当前列数:" + j);

                    Cell cell = row.getCell(j);

                    // 获取得到字符串
                    String content = "";
                    try {
                        // 转换为字符串类型
                        cell.setCellType(CellType.STRING);
                        content = cell.getStringCellValue();
                    } catch (Exception e) {
                        log.warn("当前行:" + i + " 当前列数:" + j + " 转换值类型为String错误,默认为空");
                    }

                    // 后面根据需要以此类推
                    // log.debug("行数(" + i + ") 列号(" + j + "):" + content);
                    cellList.add(content);
                }
                rowList.add(cellList);

            }
            ExcelList.add(rowList);
        }
        return ExcelList;
    }

    public static String getFileExtension(String fileName) {
        if (fileName.lastIndexOf(".") != -1 && fileName.lastIndexOf(".") != 0) {
            return fileName.substring(fileName.lastIndexOf(".") + 1).toLowerCase();
        } else {
            return "";
        }
    }

}
